package com.sfy.les.dictionary.mysql;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.sfy.les.db.DBConnection;
import com.sfy.les.dictionary.DataBaseDictionary;
import com.sfy.les.dictionary.DictionaryInfo;
import com.sfy.les.table.TableInfo;
import com.sfy.les.utils.Common;

public class MysqlDictionaryInfo extends DictionaryInfo{
	
	@Override
	public String getQueryAllTablesInfoSql(DataBaseDictionary exportParam) {
		String querySql = "select TABLE_NAME,TABLE_COMMENT TABLE_DESC from information_schema.tables t where table_schema= '"+exportParam.getDbName()+"'";
		if(!Common.checkNullOrEmpty(exportParam.getTableNamePrefix())){
			querySql += " AND TABLE_NAME like '" + exportParam.getTableNamePrefix() + "%'";
		}
		return querySql;
	}

	@Override
	public String getQueryTableFieldsInfoSql(DataBaseDictionary exportParam, TableInfo tableInfo) {
		String querySql = "SELECT DISTINCT t.COLUMN_NAME FIELD_NAME, t.DATA_TYPE FIELD_TYPE, IFNULL(IFNULL(t.NUMERIC_PRECISION,t.CHARACTER_MAXIMUM_LENGTH),'NULL') FIELD_LENGTH,  IFNULL(t.NUMERIC_SCALE,0) FIELD_DECIMAL_LENGTH, if(t.IS_NULLABLE='YES','允许','不允许') FIELD_IS_EMPTY, if(t.COLUMN_KEY='PRI','是','否') FIELD_IS_PRIMARY_KEY, IF( 	t.EXTRA = 'auto_increment', 	'是', 	'否' ) FIELD_IS_AUTOINCERE, IFNULL(t.COLUMN_DEFAULT,'NULL') FIELD_DEFAULT_VALUE, IFNULL(t.COLUMN_COMMENT,'NULL') FIELD_DESC FROM information_schema.COLUMNS t WHERE TABLE_SCHEMA = '"+exportParam.getDbName()+"' AND TABLE_NAME = '"+tableInfo.getTableName()+"'";
		return querySql;
	}

	@Override
	public String getQueryTableIndexInfoSql(DataBaseDictionary exportParam, TableInfo tableInfo) {
		String querySql = "SELECT IF (NON_UNIQUE = 0, 'Unique', 'Normal') INDEX_TYPE, INDEX_TYPE INDEX_STYLE, UPPER(INDEX_NAME) INDEX_NAME, UPPER(GROUP_CONCAT(COLUMN_NAME)) INDEX_FIELDS FROM 	information_schema.STATISTICS WHERE 	TABLE_SCHEMA = '"+exportParam.getDbName()+"' AND TABLE_NAME = '"+tableInfo.getTableName()+"' GROUP BY INDEX_NAME ORDER BY INDEX_TYPE desc";
		return querySql;
	}

	@Override
	public String getTableDdl(DataBaseDictionary exportParam, TableInfo tableInfo) throws Exception {
		String querySql = "show create table `" + tableInfo.getTableName() + "`";
		Connection conn = null;
		Statement ps = null;
		ResultSet rs = null;
		//建表语句
		String ddl = "";
		try {
			conn = DBConnection.getConnection(exportParam);
			if(conn == null){
				throw new Exception("获取数据库连接失败！");
			}
			ps = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
			rs = ps.executeQuery(querySql);
        	while (rs.next()) {
        		ddl = rs.getString(rs.getMetaData().getColumnName(2));
			}
		}catch (SQLException e) {
			throw new Exception(e);
		}finally{			
			DBConnection.closeConnection(conn, ps, rs);
		}
		return ddl;
	}
	
}
